package models;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;

import com.trackvoodoo.models.CarDocument.Car;
import com.trackvoodoo.models.CarDocument.Car.CarComment;
import com.trackvoodoo.models.CarDocument.Car.Modifications;
import com.trackvoodoo.models.CarDocument.Car.Picture;
import com.trackvoodoo.models.UserDocument.User;
import com.trackvoodoo.models.UserDocument.User.CurrentAchivements;
import com.trackvoodoo.models.UserDocument.User.Friends;
import com.trackvoodoo.models.UserDocument.User.TrackDate;

import database.connector.DatabaseFactory;
import database.live.DOAStatements;
import database.live.MySqlDAO;
import engine.EngineUtils;

public class UserModel {

	private MySqlDAO d = null;
	private int userId = 0;
	private PreparedStatement p = null;
	private User userInfo = null;
	
	public UserModel(String user)
	{
		populateUserInfo(user);
	}
	
	public User getUser()
	{
		return userInfo;
	}
	
	
	/*
	 * private build up methods.
	 */
	
	private boolean populateUserInfo(String user)
	{
		d = (MySqlDAO) DatabaseFactory.getDAO();
		if (d.isOpen())
		{
			userInfo = User.Factory.newInstance();
			userInfo.setName(user);
			
			
			
			try{
				//First lets the user information
				setUserDetails();
				//Now get the car information
				getCarInformation();
				//Now need to drill down for each car and get the other bits needed.
				getCarFurtherDetails();
				//Now populate any track dates.
				getTrackDates();
				//Get user points
				getUserPoints();
				//Get friends
				getFriends();
				
			
			}catch (SQLException e)
			{
				System.out.println(e.getMessage());
				userInfo = null;
				return false;
			}
						
			//Close the connection
			finally{
			d.closeConnection();
			}
		}
		
		
		return true;
	}
	
	private void setUserDetails() throws SQLException
	{
		p = d.getPreparedStatement(DOAStatements.SELECT_USER_INFORMATION);
		p.setString(1, userInfo.getName());
		ResultSet rs = p.executeQuery();
		rs.next();
		
		userInfo.setLoginName(rs.getString(1));
		userInfo.setAvatarPath(rs.getString(2));
		
		userId = rs.getInt(3); //CURRENT USER ID TO USE TO CROSS REFERENCE
	}
	
	private void getCarInformation() throws SQLException
	{
		p = d.getPreparedStatement(DOAStatements.SELECT_CAR_INFORMATION);
		p.setInt(1,userId);
		ResultSet rs = p.executeQuery();
		
		while (rs.next())
		{
			Car c = userInfo.addNewCar();
			
			c.setMake(rs.getString(1));
			c.setModel(rs.getString(2));
			c.setDescription(rs.getString(3));
			c.setCarRef(rs.getInt(4));
		}
	}
	
	private void getCarFurtherDetails() throws SQLException
	{
		Car[] carArray = userInfo.getCarArray();
		if (carArray.length > 0)
		{
			for (Car c: carArray)
			{
				//For each car buildup the complete ref frame
				//1) get the pictures
				p = d.getPreparedStatement(DOAStatements.SELECT_CAR_PICTURES);
				p.setInt(1,userId);
				p.setInt(2, c.getCarRef());
				ResultSet rs = p.executeQuery();
				while (rs.next())
				{
					Picture pic = c.addNewPicture();
					pic.setPictureLocation(rs.getString(1));
					pic.setPictureDescription(rs.getString(2));
				}
				
				//2) get the car comments
				p = d.getPreparedStatement(DOAStatements.SELECT_CAR_COMMENTS);
				p.setInt(1, c.getCarRef());
				rs = p.executeQuery();
				while (rs.next())
				{
					CarComment comment = c.addNewCarComment();
					comment.setPoster(rs.getString(1));
					comment.setComment(rs.getString(2));
					Date date = rs.getDate(3);
					Calendar cal=Calendar.getInstance();
					cal.setTime(date);
					comment.setDatePosted(cal);	
				}
				
				//3 modifications
				p = d.getPreparedStatement(DOAStatements.SELECT_CAR_MODS);
				p.setInt(1, c.getCarRef());
				rs = p.executeQuery();
				while (rs.next())
				{
					Modifications m = c.addNewModifications();
					m.setName(rs.getString(1));
					m.setDescription(rs.getString(2));
					m.setCost(new BigDecimal(rs.getInt(3)));
				}
			}
		}
	}
	
	private void getTrackDates() throws SQLException
	{
		p = d.getPreparedStatement(DOAStatements.SELECT_TRACK_DATES);
		p.setInt(1,userId);
		ResultSet rs = p.executeQuery();
		
		while (rs.next())
		{
			//Got a track day, so now need to populate and drill to get more info.
			TrackDate tDate = userInfo.addNewTrackDate();
			tDate.setComplete(rs.getBoolean(4));
			Date date = rs.getDate(2);
			Calendar cal=Calendar.getInstance();
			cal.setTime(date);
			tDate.setDate(cal);
			
			//Get the car
			p = d.getPreparedStatement(DOAStatements.SELECT_TRACK_DATES_CAR);
			p.setInt(1,rs.getInt(5)); //carId
			ResultSet rsCar = p.executeQuery();
			while (rsCar.next())
			{
				//only be one, get the make and the modeland combie the string
				tDate.setTakingCar(rsCar.getString(1) + " " + rsCar.getString(2));
			}
			
			//Get the track name
			p = d.getPreparedStatement(DOAStatements.SELECT_TRACK_DATES_TRACK);
			p.setInt(1,rs.getInt(1));  //track ref
			ResultSet rsTrack = p.executeQuery();
			int trackRef = 0;
			while (rsTrack.next())
			{
				//only one
				trackRef = rsTrack.getInt(1);
				tDate.setTrackEvent(rsTrack.getString(2));
				
			}	
			
			//Get track 
			if (trackRef != 0)
			{
				p = d.getPreparedStatement(DOAStatements.SELECT_TRACK_DATES_TRACKDETAILS);
				p.setInt(1,trackRef); //trackif
				ResultSet rsTrackDetails = p.executeQuery();
				while (rsTrackDetails.next())
				{
					tDate.setTrackname(rsTrackDetails.getString(1));
				}
			}	
		}
	}
	
	private void getUserPoints() throws SQLException
	{
		p = d.getPreparedStatement(DOAStatements.SELECT_USERPOINTS); //achivementId, complete, currentPoints, dataComplete
		p.setInt(1,userId);
		ResultSet rs = p.executeQuery();
		
		while (rs.next())
		{
			CurrentAchivements c = userInfo.addNewCurrentAchivements();
			c.setComplete(rs.getBoolean(2));
			c.setCurrentPoints(rs.getInt(3));
			if (rs.getBoolean(2)) //if complete
			{
				c.setDateComplete(EngineUtils.dateToCalendar(rs.getDate(4)));
			}
			
			//need to get other info no
			PreparedStatement pp = d.getPreparedStatement(DOAStatements.SELECT_USERPOINTSDETAILS); //description, notJpg, gotJpg
			pp.setInt(1, userId);
			ResultSet ppSet = pp.executeQuery();
			
			while (ppSet.next()) //Should be just one ...
			{
				c.setDescription(ppSet.getString(1));
			}
			
			if (rs.getBoolean(2)) // is complete?
			{
				c.setJpgGfx(ppSet.getString(3)); // gotJpg
			}
			else
			{
				c.setJpgGfx(ppSet.getString(2)); //not gotJpg
			}
		}
	}
	
	private void getFriends() throws SQLException
	{
		
		//Get count
		p = d.getPreparedStatement(DOAStatements.COUNT_FRIENDS);
		ResultSet rs = p.executeQuery();
		while (rs.next())
		{
			userInfo.setFriendCount(rs.getInt(1));
		}
		
				
		p = d.getPreparedStatement(DOAStatements.SELECT_FRIENDS); //friend id.
		p.setInt(1,userId);
		rs = p.executeQuery();
		
		while (rs.next()) //for each friedn id
		{
			PreparedStatement p2 = d.getPreparedStatement(DOAStatements.SELECT_USER_BY_FRIEND_ID);
			p2.setInt(1, rs.getInt(1));
			ResultSet rs2 = p2.executeQuery();
			
			while (rs2.next())
			{
				Friends f = userInfo.addNewFriends();
				f.setUserId(rs.getInt(1));
				f.setName(rs2.getString(2));
				f.setJoinDate(EngineUtils.dateToCalendar(rs2.getDate(3)));
				f.setAvatar(rs2.getString(4));
			}
			
		}
		
	}
}
